﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace JU.DAL
{
    public class SqlHelper
    {
        private readonly string _connStr;
        private readonly int _commandTimeOut = 60;   //有待考究

        /// <summary>
        /// 根据ConnectionStrings名称创建SQLHelper
        /// </summary>
        /// <param name="connStr">ConnectionStrings名称</param>
        public SqlHelper(string connStr)
        {
            _connStr = ConfigurationManager.ConnectionStrings[connStr].ConnectionString;
        }

        /// <summary>
        /// 默认ConnectionStrings名称为sql
        /// </summary>
        public SqlHelper()
            : this("conn")
        {
        }

        /// <summary>
        /// 执行非插叙语句，返回受影响行数
        /// </summary>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="ps">参数列表</param>
        /// <returns>受影响行数</returns>
        public int ExecuteNonQuery(string cmdText, params SqlParameter[] ps)
        {
            return this.ExecuteNonQuery(cmdText, CommandType.Text, ps);
        }

        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="cmdText">存储过程名称</param>
        /// <param name="ps">参数列表</param>
        /// <returns>存储过程返回值</returns>
        public int ExecuteNonQuerySP(string cmdText, params SqlParameter[] ps)
        {
            return this.ExecuteNonQuery(cmdText, CommandType.StoredProcedure, ps);
        }

        /// <summary>
        /// 执行SQL语句或存储过程，返回结果
        /// </summary>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="type">执行类型</param>
        /// <param name="ps">参数列表</param>
        /// <returns>受影响行数或存储过程成功与否</returns>
        public int ExecuteNonQuery(string cmdText, CommandType type, params SqlParameter[] ps)
        {
            if (type == CommandType.Text)
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(_connStr))
                    {
                        using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                        {
                            if (ps != null)
                            {
                                cmd.Parameters.AddRange(ps);
                            }
                            conn.Open();
                            return cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (SqlException ex)
                {
                    throw ex;
                }
            }
            else if (type == CommandType.StoredProcedure)
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(_connStr))
                    {
                        using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            if (ps != null)
                            {
                                cmd.Parameters.AddRange(ps);
                            }
                            conn.Open();
                            return cmd.ExecuteNonQuery();
                        }
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 执行查询语句，返回第一行第一列数据
        /// </summary>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="ps">参数列表</param>
        /// <returns>第一行第一列数据</returns>
        public object ExecuteScalar(string cmdText, params SqlParameter[] ps)
        {
            return ExecuteScalar(cmdText, CommandType.Text, ps);
        }

        /// <summary>
        /// 执行存储过程，返回存储过程返回值
        /// </summary>
        /// <param name="cmdText">存储过程名称</param>
        /// <param name="ps">参数列表</param>
        /// <returns>存储过程返回值</returns>
        public object ExecuteScalarSP(string cmdText, params SqlParameter[] ps)
        {
            return ExecuteScalar(cmdText, CommandType.StoredProcedure, ps);
        }

        /// <summary>
        /// 执行查询语句或存储过程，返回第一行第一列数据或存储过程返回值
        /// </summary>
        /// <param name="cmdText">Sql语句或存储过程名称</param>
        /// <param name="ps">参数列表</param>
        /// <returns>第一行第一列数据或存储过程返回值</returns>
        public object ExecuteScalar(string cmdText, CommandType type, params SqlParameter[] ps)
        {
            if (type == CommandType.Text)
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(_connStr))
                    {
                        using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                        {
                            if (ps != null)
                            {
                                cmd.Parameters.AddRange(ps);
                            }
                            conn.Open();
                            return cmd.ExecuteScalar();
                        }
                    }
                }
                catch (Exception ex)
                {
                    return ex;
                }
            }
            else if (type == CommandType.StoredProcedure)
            {
                try
                {
                    for (int i = 0; i < ps.Length; i++)
                    {
                        if (ps[i].Value == null)
                        {
                            ps[i].Direction = ParameterDirection.Output;
                        }
                    }
                    using (SqlConnection conn = new SqlConnection(_connStr))
                    {
                        using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                        {
                            cmd.CommandType = type;
                            if (ps != null)
                            {
                                cmd.Parameters.AddRange(ps);
                            }
                            conn.Open();
                            cmd.ExecuteScalar();
                        }
                    }
                    List<Object> returnValue = new List<object>();
                    for (int i = 0; i < ps.Length; i++)
                    {
                        if (ps[i].Direction == ParameterDirection.Output)
                        {
                            returnValue.Add(ps[i].Value);
                        }
                    }
                    return returnValue;
                }
                catch (Exception ex)
                {
                    return ex;
                }
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 执行查询语句，并返回一个DataReader阅读器
        /// </summary>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="ps">参数列表</param>
        /// <returns>DataReader阅读器</returns>
        public SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] ps)
        {
            return ExecuteReader(cmdText, CommandType.Text, ps);
        }

        /// <summary>
        /// 执行存储过程，并返回一个DataReader阅读器
        /// </summary>
        /// <param name="cmdText">存储过程名称</param>
        /// <param name="ps">参数列表</param>
        /// <returns>DataReader阅读器</returns>
        public SqlDataReader ExecuteReaderSP(string cmdText, params SqlParameter[] ps)
        {
            return ExecuteReader(cmdText, CommandType.StoredProcedure, ps);
        }

        /// <summary>
        /// 执行查询语句或存储过程，并返回一个DataReader阅读器
        /// </summary>
        /// <param name="cmdText">Sql语句或存储过程名称</param>
        /// <param name="type">执行类型</param>
        /// <param name="ps">参数列表</param>
        /// <returns>DataReader阅读器</returns>
        public SqlDataReader ExecuteReader(string cmdText, CommandType type, params SqlParameter[] ps)
        {
            SqlConnection conn = new SqlConnection(_connStr);
            try
            {
                using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                {
                    cmd.CommandType = type;
                    if (ps != null)
                    {
                        cmd.Parameters.AddRange(ps);
                    }
                    conn.Open();
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception ex)
            {
                conn.Dispose();
                throw (ex);
            }
        }

        /// <summary>
        /// 执行查询语句，返回对象的List，要求对象的属性与查询结构的字段名称，类型完全相同
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="ps">参数列表</param>
        /// <returns>List<T></returns>
        public List<T> GetList<T>(string cmdText, params SqlParameter[] ps)
        {
            return GetList<T>(cmdText, CommandType.Text, ps);
        }

        /// <summary>
        /// 执行存储过程，返回对象的List，要求对象的属性与查询结构的字段名称，类型完全相同
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="cmdText">存储过程名称</param>
        /// <param name="ps">参数列表</param>
        /// <returns>List<T></returns>
        public List<T> GetListSP<T>(string cmdText, params SqlParameter[] ps)
        {
            return GetList<T>(cmdText, CommandType.StoredProcedure, ps);
        }

        /// <summary>
        /// 执行查询语句或存储过程，返回对象的List，要求对象的属性与查询结构的字段名称，类型完全相同
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="cmdText">Sql语句或存储过程名称</param>
        /// <param name="type">执行类型</param>
        /// <param name="ps">参数列表</param>
        /// <returns>List<T></returns>
        public List<T> GetList<T>(string cmdText, CommandType type, params SqlParameter[] ps)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(_connStr))
                {
                    using (SqlCommand cmd = new SqlCommand(cmdText, conn))
                    {
                        cmd.CommandType = type;
                        if (ps != null)
                        {
                            cmd.Parameters.AddRange(ps);
                        }
                        conn.Open();
                        using (SqlDataReader reader = cmd.ExecuteReader())
                        {
                            if (reader.HasRows)
                            {
                                List<T> list = new List<T>();
                                while (reader.Read())
                                {
                                    Type objectType = typeof(T);
                                    T model = (T)Activator.CreateInstance(objectType);
                                    for (int i = 0; i < reader.FieldCount; i++)
                                    {
                                        PropertyInfo p = objectType.GetProperty(reader.GetName(i));
                                        switch (reader.GetFieldType(i).Name)
                                        {
                                            case "String":
                                                p.SetValue(model, reader.IsDBNull(i) ? null : reader.GetString(i));
                                                break;
                                            case "Int16":
                                                p.SetValue(model, reader.IsDBNull(i) ? (short?)null : reader.GetInt16(i));
                                                break;
                                            case "Int32":
                                                p.SetValue(model, reader.IsDBNull(i) ? (int?)null : reader.GetInt32(i));
                                                break;
                                            case "Int64":
                                                p.SetValue(model, reader.IsDBNull(i) ? (long?)null : reader.GetInt64(i));
                                                break;
                                            case "Float":
                                                p.SetValue(model, reader.IsDBNull(i) ? (float?)null : reader.GetFloat(i));
                                                break;
                                            case "Double":
                                                p.SetValue(model, reader.IsDBNull(i) ? (double?)null : reader.GetDouble(i));
                                                break;
                                            case "Decimal":
                                                p.SetValue(model, reader.IsDBNull(i) ? (decimal?)null : reader.GetDecimal(i));
                                                break;
                                            case "Boolean":
                                                p.SetValue(model, reader.IsDBNull(i) ? (bool?)null : reader.GetBoolean(i));
                                                break;
                                            case "DateTime":
                                                p.SetValue(model, reader.IsDBNull(i) ? (DateTime?)null : reader.GetDateTime(i));
                                                break;
                                        }
                                    }
                                    list.Add(model);
                                }
                                return list;
                            }
                            return null;
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="ps">参数列表</param>
        /// <returns>的DataSet</returns>
        public DataSet GetDataSet(string cmdText, params SqlParameter[] ps)
        {
            return this.GetDataSet(cmdText, CommandType.Text, ps);
        }

        /// <summary>
        /// 执行存储过程返回DataSet
        /// </summary>
        /// <param name="cmdText">存储过程名</param>
        /// <param name="ps">参数列表</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSetSP(string cmdText, params SqlParameter[] ps)
        {
            return this.GetDataSet(cmdText, CommandType.StoredProcedure, ps);
        }

        /// <summary>
        /// 执行存储过程，或SQL语句，返回DataSet
        /// </summary>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="type">执行类型</param>
        /// <param name="ps">参数列表</param>
        /// <returns>DataSet</returns>
        public DataSet GetDataSet(string cmdText, CommandType type, params SqlParameter[] ps)
        {
            DataSet ds = new DataSet();
            try
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(cmdText, _connStr))
                {
                    if (ps != null)
                    {
                        sda.SelectCommand.Parameters.AddRange(ps);
                    }
                    sda.SelectCommand.CommandType = type;
                    sda.Fill(ds);
                }
                return ds;
            }
            catch (Exception ex)
            {
                ds.Dispose();
                throw (ex);
            }
        }

        /// <summary>
        /// 执行SQL语句，使用DataSet对数据表进行批量修改
        /// </summary>
        /// <param name="cmdTxt">SQL语句</param>
        /// <param name="ds">用于更新数据源的DataSet</param>
        /// <param name="ps">参数列表</param>
        /// <returns>受影响行数</returns>
        public int DataSetUpdate(string cmdTxt, DataSet ds, params SqlParameter[] ps)
        {
            return DataSetUpdate(cmdTxt, ds, CommandType.Text, ps);
        }

        /// <summary>
        /// 执行存储过程，使用DataSet对数据表进行批量修改
        /// </summary>
        /// <param name="cmdTxt">存储过程名称</param>
        /// <param name="ds">用于更新数据源的DataSet</param>
        /// <param name="ps">参数列表</param>
        /// <returns>存储过程返回值</returns>
        public int DataSetUpdateSp(string cmdTxt, DataSet ds, params SqlParameter[] ps)
        {
            return DataSetUpdate(cmdTxt, ds, CommandType.StoredProcedure, ps);
        }

        /// <summary>
        /// 执行SQL语句，或存储过程，使用DataSet对数据表进行批量修改
        /// </summary>
        /// <param name="cmdText">SQL语句或存储过程名称</param>
        /// <param name="ds">用于更新数据源的DataSet</param>
        /// <param name="type">执行类型</param>
        /// <param name="ps">参数列表</param>
        /// <returns>受影响行数或存储过程返回值</returns>
        public int DataSetUpdate(string cmdText, DataSet ds, CommandType type, params SqlParameter[] ps)
        {
            if (type == CommandType.Text)
            {
                try
                {
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmdText, _connStr))
                    {
                        if (ds != null)
                        {
                            sda.UpdateCommand = sda.SelectCommand;
                            if (ps != null)
                            {
                                sda.UpdateCommand.Parameters.AddRange(ps);
                            }
                            return sda.Update(ds);
                        }
                        return -1;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else if (type == CommandType.StoredProcedure)
            {
                try
                {
                    ps[ps.Length - 1].Direction = ParameterDirection.Output;
                    using (SqlDataAdapter sda = new SqlDataAdapter(cmdText, _connStr))
                    {
                        if (ds != null)
                        {
                            sda.UpdateCommand = sda.SelectCommand;
                            if (ps != null)
                            {
                                sda.UpdateCommand.Parameters.AddRange(ps);
                            }
                            sda.Update(ds);
                        }
                        else
                        {
                            return -1;
                        }

                    }
                    return (int)ps[ps.Length - 1].Value;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 使用DataTable批量添加数据到数据库表
        /// </summary>
        /// <param name="table">DataTable</param>
        /// <param name="tableSource">需要添加数据的数据库表</param>
        public void UseSqlBulkCopy(DataTable table, string tableSource)
        {
            try
            {
                SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(_connStr, SqlBulkCopyOptions.UseInternalTransaction);
                sqlbulkcopy.DestinationTableName = tableSource;//数据库中的表名
                sqlbulkcopy.WriteToServer(table);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 使用事务批量插入两个表，同时插入成功，只插入一张表需要回滚
        /// </summary>
        /// <param name="tableNameOne">表一数据库中表名</param>
        /// <param name="tableNameTwo">表二数据库中表名</param>
        /// <param name="dtOne">表一数据</param>
        /// <param name="dtTwo">表二数据</param>
        /// <returns>插入结果</returns>
        public bool BulkCopy(string tableNameOne, string tableNameTwo, DataTable dtOne, DataTable dtTwo)
        {
            using (SqlConnection conn = new SqlConnection(_connStr))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadUncommitted))
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
                    {
                        bulkCopy.BatchSize = 2000;
                        bulkCopy.BulkCopyTimeout = _commandTimeOut;
                        try
                        {
                            BulkCopyToTbl(tableNameOne, dtOne, bulkCopy);
                            bulkCopy.ColumnMappings.Clear();
                            BulkCopyToTbl(tableNameTwo, dtTwo, bulkCopy);
                            transaction.Commit();
                            return true;
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw ex;
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 插入表格
        /// </summary>
        /// <param name="tableName">数据库中表名</param>
        /// <param name="dt">数据</param>
        /// <param name="bulkCopy">SqlBulkCopy实例</param>
        private static void BulkCopyToTbl(string tableName, DataTable dt, SqlBulkCopy bulkCopy)
        {
            bulkCopy.DestinationTableName = tableName;
            foreach (DataColumn col in dt.Columns)
            {
                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            }
            bulkCopy.WriteToServer(dt);
        }

        #region 这段代码是网上复制下来的，有待研究
        /// <summary> 
        /// 大批量插入数据(2000每批次) 
        /// 已采用整体事物控制 
        /// </summary> 
        /// <param name="connString">数据库链接字符串</param> 
        /// <param name="tableName">数据库服务器上目标表名</param> 
        /// <param name="dt">含有和目标数据库表结构完全一致(所包含的字段名完全一致即可)的DataTable</param> 
        public void BulkCopy(string connString, string tableName, DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                conn.Open();
                using (SqlTransaction transaction = conn.BeginTransaction())
                {
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction))
                    {
                        bulkCopy.BatchSize = 2000;
                        bulkCopy.BulkCopyTimeout = _commandTimeOut;
                        bulkCopy.DestinationTableName = tableName;
                        try
                        {
                            foreach (DataColumn col in dt.Columns)
                            {
                                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                            }
                            bulkCopy.WriteToServer(dt);
                            transaction.Commit();
                        }
                        catch (Exception ex)
                        {
                            transaction.Rollback();
                            throw ex;
                        }
                        finally
                        {
                            conn.Close();
                        }
                    }
                }
            }
        }

        /// <summary> 
        /// 批量更新数据(每批次5000) 
        /// </summary> 
        /// <param name="connString">数据库链接字符串</param> 
        /// <param name="table"></param> 
        public void Update(string connString, DataTable table)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand comm = conn.CreateCommand();
            comm.CommandTimeout = _commandTimeOut;
            comm.CommandType = CommandType.Text;
            SqlDataAdapter adapter = new SqlDataAdapter(comm);
            SqlCommandBuilder commandBulider = new SqlCommandBuilder(adapter);
            commandBulider.ConflictOption = ConflictOption.OverwriteChanges;
            try
            {
                conn.Open();
                //设置批量更新的每次处理条数 
                adapter.UpdateBatchSize = 5000;
                adapter.SelectCommand.Transaction = conn.BeginTransaction();/////////////////开始事务 
                if (table.ExtendedProperties["SQL"] != null)
                {
                    adapter.SelectCommand.CommandText = table.ExtendedProperties["SQL"].ToString();
                }
                adapter.Update(table);
                adapter.SelectCommand.Transaction.Commit();/////提交事务 
            }
            catch (Exception ex)
            {
                if (adapter.SelectCommand != null && adapter.SelectCommand.Transaction != null)
                {
                    adapter.SelectCommand.Transaction.Rollback();
                }
                throw ex;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
        }
        #endregion
    }
}